package com.walnut.lom_system.common.util;

import com.walnut.lom_system.domain.dto.ExcelUserInfoDto;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ConcurrentLinkedQueue;

import static cn.hutool.poi.excel.cell.CellUtil.getCellValue;

/**
 * @author chenhetao
 * @version 1.0
 * @className ExcelImportUtil
 * @description TODO
 * @date 2022/3/8 18:59
 */
public class ExcelImportUtil {

    /**
     *
     * @param multipartFile
     * @return
     * @throws IllegalAccessException
     * @throws IOException
     */
    public List<ExcelUserInfoDto> importUserInfo(MultipartFile multipartFile) {
//1. 读取一个文件,将文件转换成工作簿(WorkBook)
        String filePath = multipartFile.getOriginalFilename();
//        String filePath = "/Users/chenhetao/Downloads/计科2021—2022两委干部联系表.xls";

        InputStream inputStream = null;
        try {
            inputStream = multipartFile.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (inputStream == null) {
            return null;
        }
        assert filePath != null;
        String suffix = filePath.substring(filePath.lastIndexOf("."));
        org.apache.poi.ss.usermodel.Workbook workbook = null;
        try {
            if(".xls".equals(suffix)){
                System.out.println("文件类型是.xls");
                workbook = new HSSFWorkbook(inputStream);
            }

            if(".xlsx".equals(suffix)){
                System.out.println("文件类型是.xlsx");
                workbook = new XSSFWorkbook(inputStream);
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("文件没有找到");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("发生io异常");
        }

        //2. 从工作簿中读取工作表
        //根据索引获取工作表
        assert workbook != null;
        Sheet sheet = workbook.getSheetAt(0);
        //3. 从工作表中读取行(在获取最后一行的时候,+1操作)

        ArrayList<ExcelUserInfoDto> excelUserInfoDtos = new ArrayList<>();

        for (int i = 0; i < sheet.getLastRowNum() + 1; i++) {
            Row row = sheet.getRow(i);
            //4. 从行中读取单元格

            if (i == 0) {
                //跳过标题行
                continue;
            }

            ConcurrentLinkedQueue concurrentLinkedQueue = new ConcurrentLinkedQueue();


            for (int j = 0; j < row.getLastCellNum(); j++) {

                org.apache.poi.ss.usermodel.Cell cell = row.getCell(j);
                String value ;
                //合并单元格处理

                if (isMergedRegion(sheet, i, cell.getColumnIndex())) {
                    value = (String) getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
                } else {
                    cell.setCellType(CellType.STRING);
                    value = cell.getRichStringCellValue().getString();
                }

                //5. 从单元格中读取值,并加入队列
                concurrentLinkedQueue.add(value);
            }

            ExcelUserInfoDto excelUserInfoDto = new ExcelUserInfoDto();
            Class excelUserInfoDtoClass = excelUserInfoDto.getClass();
            Field[] excelUserInfoDtoFields = excelUserInfoDtoClass.getDeclaredFields();
            int n = 0;
            for (Field excelUserInfoDtoField : excelUserInfoDtoFields) {
                //设置私有属性的访问权限
                excelUserInfoDtoField.setAccessible(true);
                //excelUserInfoDtoField为单个属性
                try {
                    excelUserInfoDtoField.set(excelUserInfoDto, concurrentLinkedQueue.peek());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                    return null;
                }
                concurrentLinkedQueue.remove();
            }
            excelUserInfoDtos.add(excelUserInfoDto);
            System.out.println(excelUserInfoDto);
        }
        //TODO 异常情况的处理和捕获
        return excelUserInfoDtos;
    }



    public Object getMergedRegionValue(org.apache.poi.ss.usermodel.Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    org.apache.poi.ss.usermodel.Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    private boolean isMergedRegion(org.apache.poi.ss.usermodel.Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
}
